﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Services.DAL
{
    public static class SQLiteHelper
    {
        //public static String ConnectionString = "Data Source=H:/SQLite/DB/minWeiGanBu.db;Pooling=true;FailIfMissing=false";
        private static String sConn = "Data Source=D:/ionicDB.db;Pooling=true;FailIfMissing=false;version=3;new=False;datetimeformat=CurrentCulture";
        //public const string sConn = "Data Source=" + @"path";

        public static object ExecuteScalar(string sql, params SQLiteParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, parameters);
        }


        public static object ExecuteScalar(string sql, CommandType type, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(sConn))
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                cmd.CommandType = type;
                cmd.Parameters.AddRange(parameters);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return obj;
            }
        }


        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, parameters);
        }

        public static int ExecuteNonQuery(string sql, CommandType type, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(sConn))
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                cmd.CommandType = type;
                cmd.Parameters.AddRange(parameters);
                int num = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return num;
            }
        }

        public static DataTable ExecuteTable(string sql, params SQLiteParameter[] parameters)
        {
            return ExecuteTable(sql, CommandType.Text, parameters);
        }


        public static DataTable ExecuteTable(string sql, CommandType type, params SQLiteParameter[] parameters)
        {
            SQLiteConnection conn = new SQLiteConnection(sConn);
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            cmd.CommandType = type;
            cmd.Parameters.AddRange(parameters);
            SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            conn.Close();
            DataTable dt = ds.Tables[0];
            return dt;
        }

        
    }
    public static class ModelConvertHelper // 此处一定要加上new()
    {

        public static IList<T> ToList<T>(DataTable table) where T : new()
        {
            IList<T> entities = new List<T>();
            foreach (DataRow row in table.Rows)
            {
                T entity = new T();
                foreach (var item in entity.GetType().GetProperties())
                {
                    item.SetValue(entity, Convert.ChangeType(row[item.Name], item.PropertyType), null);
                }
                entities.Add(entity);
            }
            return entities;
        }
        public static T ToEntity<T>(DataTable table) where T : new()
        {
            T entity = new T();
            foreach (DataRow row in table.Rows)
            {
                foreach (var item in entity.GetType().GetProperties())
                {
                    if (row.Table.Columns.Contains(item.Name))
                    {
                        if (DBNull.Value != row[item.Name])
                        {
                            item.SetValue(entity, Convert.ChangeType(row[item.Name], item.PropertyType), null);
                        }

                    }
                }
            }

            return entity;
        }

    }
}
